package starkuz.lg.dao.impl.visits;

import starkuz.lg.dao.ifaces.visits.VisitCtrDao;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;

@Repository
public class VisitCtrDaoImpl implements VisitCtrDao {


    private static final String INSERT_VISIT = "insert into ctr_visits (siteId, visitDate) values (?, ?)";
    private static final String COUNT_ALL_VISITS_BY_SITE_ID = "select count(*) value from ctr_visits where siteId = ?";
    private static final String COUNT_ALL_VISITS_BETWEEN_DATES =
            "select count(*) value from ctr_visits where siteId = ?  and visitDate between ? and ? ";

    @Autowired
    private BasicDataSource ds;

    public void setDs(BasicDataSource ds) {
        this.ds = ds;
    }

    @Override
    public void addVisit(Integer idPage) {  //добавление визита в БД; необходим только id страницы, чтобы узнать больше
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(INSERT_VISIT);
            psql.setInt(1, idPage);
            psql.setDate(2, new Date(new java.util.Date().getTime()));
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Integer countAllVisitsOfPage(Integer siteId) {
        Integer value = null;
        Connection con = null;
        PreparedStatement psql = null;
        ResultSet rs = null;
        try {
            con = ds.getConnection();
            try {
                psql = con.prepareStatement(COUNT_ALL_VISITS_BY_SITE_ID);
                psql.setInt(1, siteId);
                rs = psql.executeQuery();
                while (rs.next()) {
                    value = rs.getInt("value");
                }
                rs.close();
            } catch (SQLException s) {
                s.printStackTrace();
            }
            con.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }

    @Override
    public Integer countAllVisitsOfPageBetweenDates(Integer siteId, Date dateStart, Date dateEnd) {
        Integer value = null;
        Connection con = null;
        PreparedStatement psql = null;
        ResultSet rs = null;
        try {
            con = ds.getConnection();
            try {
                psql = con.prepareStatement(COUNT_ALL_VISITS_BETWEEN_DATES);
                psql.setInt(1, siteId);
                psql.setDate(2, dateStart);
                psql.setDate(3, dateEnd);
                rs = psql.executeQuery();
                while (rs.next()) {
                    value = rs.getInt("value");
                }
                rs.close();
            } catch (SQLException s) {
                s.printStackTrace();
            }
            con.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }
}
